# -*- coding: utf-8 -*-
# !/usr/bin/python
__author__ = 'bitfeng'

from scrapy.utils.python import str_to_unicode, unicode_to_str
from scrapyluke.db_connect import PostgreSQLConnect
from scrapyluke.itemsFields import *
from scrapyluke.commonfun import renameFile

POSTGRESQL_URI_16 = {
        'host': '192.168.16.6',
        'port': 5432,
        'database': 'source_xsbcc',
        'user': 'wy_gather',
        'password': 'wygather',
}

POSTGRESQL_URI = {
    'host': '127.0.0.1',
    'port': 5432,
    'database': 'postgres',
    'user': 'postgres',
    'password': '100525',
}

POSTGRESQL_URI_ETL = {
        'host': '192.168.31.7',
        'port': 5432,
        'database': 'postgres',
        'user': 'yice',
        'password': 'cece',
}


# 通过百度翻译的结果，自动生成Item类，生成创建table相应的sql语句, 创建数据库table, 获取ItemLoader语句
class InitScrapy(object):

    def __init__(self, item=None, initdict=None):
        if initdict:
            self.itemdict = self.getItemdict(item=item, initdict=initdict)
        else:
            self.basedict = {'数据源的url': 'url', '数据源的获取时间': 'insert_time'}
            self.itemdict = self.getItemdict(item=item, initdict=self.basedict)

    def getItemdict(self, item=None, initdict=None):
        itemdict = initdict if initdict else {}
        self.itemdictkeys_sort = initdict.keys() if initdict else []
        if item:
            for field_name in item.split('\n'):
                field_name = field_name.strip()
                if field_name:
                    uchar = str_to_unicode(field_name)
                    if field_name[0] == '#':
                        continue
                    elif uchar[0] >= u'\u4e00' and uchar[0] <= u'\u9fa5':
                        itemdictkey = unicode_to_str(uchar)
                        self.itemdictkeys_sort.append(itemdictkey)
                    elif (uchar[0] >= u'\u0041' and uchar[0]<=u'\u005a') or (uchar[0]>=u'\u0061' and uchar[0]<=u'\u007a'):
                        if 'itemdictkey' in dir():
                            itemdict[itemdictkey] = unicode_to_str(renameFile(uchar).lower())
                            itemdictkey = ''
        return itemdict

    def getItemClass(self, itemname, tab='    '):
        classlist = ['class %s(Item):' % itemname]

        for key in self.itemdictkeys_sort:
            classlist.append(tab+'# %s' % key)
            classlist.append(tab+'%s = Field()' % self.itemdict[key])
        return ''.join([line+'\n' for line in classlist])

    def createTableSQLI(self ,tablename):
        sqlichar = ',\n'.join(['"%s" varchar COLLATE "default"' % self.itemdict[key] for key in self.itemdictkeys_sort])
        sqlicomments = ''.join(['COMMENT ON COLUMN "%s"."%s" IS %s;\n'
                                % (tablename.lower() ,self.itemdict[key], "'"+key+"'") for key in self.itemdictkeys_sort])
        sqli = 'CREATE TABLE "%s"\n' \
               '(\n' \
               '"id" SERIAL primary key NOT NULL,\n' \
               '%s\n' \
               ');\n' \
               '%s' % (tablename.lower(), sqlichar, sqlicomments)
        return sqli

    def createTablePostgreSQL(self, tablename, sql_uri):
        try:
            conn = PostgreSQLConnect.createConn(sql_uri)
        except IOError:
            print 'Create connect fail'
            exit(0)
        sqli = self.createTableSQLI(tablename)
        try:
            cur = conn.cursor()
            cur.execute("SELECT tablename FROM pg_catalog.pg_tables where schemaname='public' and tableowner='%s'" % sql_uri['user'])
            # print cur.fetchall()
            # print ('%s' % tablename.lower(),)
            if ('%s' % tablename.lower(),) in cur.fetchall():
                print '[WARNING] Table %s Exists' % tablename.lower()
            else:
                cur.execute(sqli)
                conn.commit()
        except IOError:
            print 'Create fail'
        conn.close()

    def getAddXpath(self, itemname='ItemClass', itemloader='ItemLoader'):
        l = 'loader = %s(item=%s(), response=response)\n' % (itemloader, itemname)
        xpath_adds = ["loader.add_xpath('%s', 'XPATH_HERE', TakeFirstL(), re='RE_HERE')\n" % self.itemdict[key] for key in self.itemdictkeys_sort]
        if "loader.add_xpath('url', 'XPATH_HERE', TakeFirstL(), re='RE_HERE')\n" in xpath_adds:
            xpath_adds[xpath_adds.index("loader.add_xpath('url', 'XPATH_HERE', "
                                        "TakeFirstL(), re='RE_HERE')\n")] = "loader.add_value('url', response.url)\n"
        if "loader.add_xpath('insert_time', 'XPATH_HERE', TakeFirstL(), re='RE_HERE')\n" in xpath_adds:
            xpath_adds[xpath_adds.index("loader.add_xpath('insert_time', 'XPATH_HERE', "
                                        "TakeFirstL(), re='RE_HERE')\n")] = "loader.add_value('insert_time', datetime.datetime.now())\n"
        return l+''.join(xpath_adds)


def createItemSQLITable(item=None, itemname=None, tablename=None, sql_uri=None, pitem=False, pSQlI=False, cTable=False):
    if item and itemname or tablename:
        initScrapy = InitScrapy(item=item)
        if pitem:
            print initScrapy.getItemClass(itemname=itemname.strip() if itemname else tablename.strip())
        if pSQlI:
            print initScrapy.createTableSQLI(tablename=tablename.strip() if tablename else itemname.strip())
        if cTable:
            if sql_uri:
                initScrapy.createTablePostgreSQL(tablename=tablename.strip() if tablename else itemname.strip(), sql_uri=sql_uri)
            else:
                'There is no SQL_URI'
        return initScrapy
    else:
        print 'There is no item' if not item else 'There is no itemname or tablename'


def createProcessUrl(sql_uri=None, name_list=None, tablename='process_urls'):
    if not name_list:
        name_list = ['add_time', 'update_time', 'url', 'spidername', 'rank', 'status', 'html', 'refer']
    if sql_uri:

        sqlichar = ',\n'.join(['"%s" varchar COLLATE "default"' % name for name in name_list])
        sqli = 'CREATE TABLE "%s"\n' \
               '(\n' \
               '"id" SERIAL primary key NOT NULL,\n' \
               '%s\n' \
               ');\n' % (tablename, sqlichar)

        conn = PostgreSQLConnect.createConn(sql_uri)
        cur = conn.cursor()
        cur.execute("SELECT tablename FROM pg_catalog.pg_tables where schemaname='public' and tableowner='%s'" % sql_uri['user'])
        # print cur.fetchall()
        # print ('%s' % tablename.lower(),)
        if ('%s' % tablename.lower(),) in cur.fetchall():
            print '[WARNING] Table %s Exists' % tablename.lower()
        else:
            cur.execute(sqli)
            conn.commit()
        conn.close()


if __name__ == '__main__':
    # init_index = createItemSQLITable(item=item_ecpa_index, itemname=itemname_ecpa_index, sql_uri=POSTGRESQL_URI, pitem=True, pSQlI=False, cTable=True)
    # print init_index.getAddXpath(itemname=itemname_ecpa_index)
    # init_info = createItemSQLITable(item=item_ecpa_info, itemname=itemname_ecpa_info, sql_uri=POSTGRESQL_URI, pitem=True, pSQlI=False, cTable=True)
    # print init_info.getAddXpath(itemname=itemname_ecpa_info)
    # createProcessUrl(sql_uri=POSTGRESQL_URI)
    # createItemSQLITable(item=CompanyProfile, itemname='companyprofile', sql_uri=POSTGRESQL_URI_16, pSQlI=True, cTable=True)
    # createItemSQLITable(item=XsbccItem, itemname='xsbccitem', sql_uri=POSTGRESQL_URI_16, pSQlI=True, cTable=True)
    # createItemSQLITable(item=SeniorExecutive, itemname='seniorexecutive', sql_uri=POSTGRESQL_URI_16, pSQlI=True, cTable=True)
    # createItemSQLITable(item=HistoricalEvolution, itemname='historicalevolution', sql_uri=POSTGRESQL_URI_16, pSQlI=True, cTable=True)
    # createItemSQLITable(item=EquityStatus, itemname='equitystatus', sql_uri=POSTGRESQL_URI_16, pSQlI=True, cTable=True)
    # createProcessUrl(sql_uri=POSTGRESQL_URI_16)
    # a = createItemSQLITable(item=itemproxy, itemname='item_proxy', sql_uri=POSTGRESQL_URI, pitem=True, cTable=True)
    # print a.getAddXpath(itemname='item_proxy')
    # createItemSQLITable(sql_uri=POSTGRESQL_URI_ETL, item=cominfo, itemname='com_info', pitem=True, cTable=True)
    createItemSQLITable(sql_uri=POSTGRESQL_URI, item=wenshu, itemname='wenshu', pitem=True, pSQlI=True, cTable=True)